'use strict';

const PICTURE_TABLE = 'picture';
const MAP_TABLE = 'pic_map_tag';
const RECYCLER_TABLE = 'recycler';
const ERR_CODE = require('../constants/errorCode');
const Service = require('egg').Service;
const sizeOf = require('image-size');
const fs = require('fs');
const path = require('path');

class PictureService extends Service {
  streamToBuffer(stream) {
    return new Promise((resolve, reject) => {
      const buffers = [];
      stream.on('error', reject);
      stream.on('data', data => { buffers.push(data); });
      stream.on('end', () => resolve(Buffer.concat(buffers)));
    });
  }

  async upload(userId) {
    const { ctx, app } = this;
    const parts = ctx.multipart({ requireFile: false });
    let stream;
    let albumId;
    const picIds = [];
    let num = 0;
    while ((stream = await parts()) != null) {
      if (!stream.fieldname) {
        albumId = stream[1];
      } else {
        const filename = stream.filename;
        const filePath = path.resolve('/Users/sam/Project/DCMH-PYTORCH/DCMH/upload/', `${stream.filename}`);
        const fileSize = stream._readableState.length;
        if (!fs.existsSync(filePath)) {
          fs.writeFileSync(filePath, '');
        }
        await stream.pipe(fs.createWriteStream(filePath));

        const stream1 = await fs.createReadStream(filePath);

        // 获取上传的图片链接
        const fileLink = await ctx.service.oss.uploadFile('profile', stream1);
        // 获取图片款高
        const stream2 = await fs.createReadStream(filePath);
        const buffer = await this.streamToBuffer(stream2);
        const dimensions = sizeOf(buffer);
        const width = dimensions.width;
        const height = dimensions.height;
        const hashCode = await ctx.service.dcmh.generanteImageCode(filePath);
        const result = await app.mysql.insert(PICTURE_TABLE, {
          album_id: albumId,
          user_id: userId,
          hash_code: hashCode,
          file_name: filename,
          file_link: fileLink,
          file_size: fileSize,
          width,
          height,
          status: 0,
          create_time: app.mysql.literals.now,
          update_time: app.mysql.literals.now,
        });
        picIds.push(result.insertId);
        await fs.unlink(filePath, err => {
          if (err) throw (err);
        });
        num++;
      }
    }
    return { num, picIds };
  }

  async move(albumId, picIds) {
    const { ctx, app } = this;
    const conn = await app.mysql.beginTransaction();
    try {
      // const sql =  `UPDATE ${PICTURE_TABLE} SET album_id = '?', update_time = '?' WHERE pic_id IN (?)`
      const row = {
        album_id: albumId,
        update_time: app.mysql.literals.now,
      };

      const options = {
        where: {
          pic_id: picIds,
        },
      };
      await conn.update(PICTURE_TABLE, row, options);

      await conn.commit();
      return true;
    } catch (err) {
      await conn.rollback();
      ctx.throw(422, '移动照片至其他相册失败', {
        errCode: ERR_CODE.PictureMove_DBUpdateFail,
        description: '数据库更新失败',
      });
    }
  }

  async destory(userId, picIds) {
    const { ctx, app } = this;
    const conn = await app.mysql.beginTransaction();
    try {
      // 更新照片的状态
      const row = {
        status: 1,
        update_time: app.mysql.literals.now,
      };
      const options = {
        where: {
          pic_id: picIds,
        },
      };
      await conn.update(PICTURE_TABLE, row, options);

      const values = [];
      // 在回收站中添加这些照片
      picIds.forEach(item => {
        values.push({ user_id: userId,
          pic_id: item,
          create_time: app.mysql.literals.now }
        );
      });

      await conn.insert(RECYCLER_TABLE, values);

      // 提交事务
      await conn.commit();
      return true;
    } catch (err) {
      await conn.rollback();
      ctx.throw(422, '删除照片至回收站失败', {
        errCode: ERR_CODE.PictureDestory_DBUpdateFail,
        description: '数据库更新失败',
      });
    }
  }

  async index(userId, page, size) {
    const { app } = this;
    const sql1 = `SELECT l.pic_id, l.album_id, o.name AS album_name,l.hash_code, l.file_name, l.file_link, l.file_size, l.height, l.width, l.status, GROUP_CONCAT(r.tags) AS tags FROM (picture AS l LEFT JOIN album AS o ON l.album_id = o.album_id) LEFT JOIN (SELECT m.pic_id, CONCAT(n.tag_id,':',n.tag_name) AS tags FROM pic_map_tag AS m LEFT JOIN tag AS n ON m.tag_id = n.tag_id) AS r ON l.pic_id = r.pic_id WHERE l.user_id = '${userId}' AND l.status!='1' GROUP BY l.pic_id ORDER BY l.create_time DESC, l.pic_id DESC LIMIT ${size} OFFSET ${(page - 1) * size}; `;
    const list = await app.mysql.query(sql1);
    // const list = await app.mysql.select(PICTURE_TABLE, {
    //   where: { user_id: userId, status: [ 0, 2 ] },
    //   columns: [ 'pic_id', 'album_id', 'hash_code', 'file_name', 'file_link', 'file_size', 'height', 'width', 'status', 'create_time', 'update_time' ], // 要查询的表字段
    //   orders: [[ 'create_time', 'desc' ], [ 'pic_id', 'desc' ]], // 排序方式
    //   limit: size, // 返回数据量
    //   offset: (page - 1) * size, // 数据偏移量
    // });

    const sql2 = `SELECT count(*) AS total FROM ${PICTURE_TABLE} WHERE status != '1' AND user_id = '?';`;
    const result = await app.mysql.query(sql2, userId);
    return { list, total: result[0].total };
  }

  async albumIndex(userId, albumId, page, size) {
    const { app } = this;
    const sql1 = `SELECT l.pic_id, l.album_id, l.hash_code, l.file_name, l.file_link, l.file_size, l.height, l.width, l.status, GROUP_CONCAT(r.tags) AS tags FROM picture AS l LEFT JOIN (SELECT m.pic_id, CONCAT(n.tag_id,':',n.tag_name) AS tags FROM pic_map_tag AS m LEFT JOIN tag AS n ON m.tag_id = n.tag_id) AS r ON l.pic_id = r.pic_id WHERE l.user_id = '${userId}' AND l.album_id = '${albumId}' AND l.status!='1' GROUP BY l.pic_id ORDER BY l.create_time DESC, l.pic_id DESC LIMIT ${size} OFFSET ${(page - 1) * size}; `;
    const list = await app.mysql.query(sql1);
    const sql2 = `SELECT count(*) AS total FROM ${PICTURE_TABLE} WHERE status != '1' AND user_id = '${userId}' AND album_id = '${albumId}';`;
    const result = await app.mysql.query(sql2);
    console.log(result);
    return { list, total: result[0].total };
  }

  async searchByText(userId, text) {
    const { ctx, app } = this;
    const enText = await ctx.service.translater.translate(text);
    const textCode = await ctx.service.dcmh.generanteTextCode(enText);
    const sql = `SELECT l.pic_id, l.album_id, o.name AS album_name,l.hash_code, l.file_name, l.file_link, l.file_size, l.height, l.width, l.status, GROUP_CONCAT(r.tags) AS tags FROM (picture AS l LEFT JOIN album AS o ON l.album_id = o.album_id) LEFT JOIN (SELECT m.pic_id, CONCAT(n.tag_id,':',n.tag_name) AS tags FROM pic_map_tag AS m LEFT JOIN tag AS n ON m.tag_id = n.tag_id) AS r ON l.pic_id = r.pic_id WHERE l.user_id = '${userId}' AND l.status!='1' GROUP BY l.pic_id ORDER BY l.create_time DESC, l.pic_id DESC; `;
    const list = await app.mysql.query(sql);
    const list1 = list.map(item => {
      item.similarity = ctx.service.dcmh.getSimilarity(textCode, item.hash_code);
      return item;
    });

    const list2 = list1.sort((a, b) => {
      const x = a.similarity;
      const y = b.similarity;
      return ((x > y) ? ((x < y) ? 1 : 0) : -1);
    });

    const finalList = list2.slice(0, 5);
    return { list: finalList };
  }

  async searchByTag(userId, tagId, page, size) {
    const { app } = this;

    const sql1 = `SELECT l.pic_id, l.album_id, o.name AS album_name, l.hash_code, l.file_name, l.file_link, l.file_size, l.height, l.width, l.status, GROUP_CONCAT(r.tags) AS tags FROM (${PICTURE_TABLE} AS l LEFT JOIN album AS o ON l.album_id = o.album_id) LEFT JOIN (SELECT m.pic_id, CONCAT(n.tag_id,':',n.tag_name) AS tags FROM pic_map_tag AS m LEFT JOIN tag AS n ON m.tag_id = n.tag_id) AS r ON l.pic_id = r.pic_id WHERE l.status != '1' AND l.user_id = '${userId}' AND l.pic_id IN (SELECT pic_id FROM ${MAP_TABLE} WHERE tag_id = '${tagId}') GROUP BY l.pic_id ORDER BY l.create_time DESC, l.pic_id DESC LIMIT ${size} OFFSET ${(page - 1) * size};`;
    const list = await app.mysql.query(sql1);

    const sql2 = `SELECT count(*) AS total FROM ${PICTURE_TABLE} WHERE status != '1' AND user_id = '?' AND pic_id IN (SELECT pic_id FROM ${MAP_TABLE} WHERE tag_id = '?');`;
    const total = await app.mysql.query(sql2, userId, tagId);
    return { list, total };
  }
}

module.exports = PictureService;
